﻿using Microsoft.Data.SqlClient;
using HRMSystemShi.Model;
using System.Data;

namespace HRMSystemShi.DAL
{
    public class SalarySheetService
    {
        public static List<int> GetYearList()
        {
            List<int> yearList = new List<int>();
            for(int i = 2000; i <= DateTime.Now.Year; i++)
            {
                yearList.Add(i);
            }
            return yearList;
        }
        public static List<int> GetMonthList(int _year)
        {
            List<int> monthList = new List<int>();
            if (_year == DateTime.Now.Year)
            {
                for(int i=1;i<= DateTime.Now.Month; i++)
                {
                    monthList.Add(i);
                }
            }
            else
            {
                for(int i = 1; i <= 12; i++)
                {
                    monthList.Add(i);
                }
            }
            return monthList;     
        }

        public static bool BuildNewSalarySheetAndItem(SalarySheet salarySheet)
        {
            string sqlNewSalarySheet = "Insert into SalarySheet(Id,Year,Month,DepartmentId)Values(@Id,@Year,@Month,@DepartmentId)";
            SqlHelper.ExecuteNonQuery(sqlNewSalarySheet,new SqlParameter[]
            {
                new SqlParameter("@Id",salarySheet.Id),
                new SqlParameter("@Year",salarySheet.Year),
                new SqlParameter("@Month",salarySheet.Month),
                new SqlParameter("@DepartmentId",salarySheet.DepartmentId),
            });
            Employee employee = new Employee()
            {
                DepartmentId = salarySheet.DepartmentId
            };     
            DataTable dt=EmployeeService.GetEmployeeList(employee);
            bool result = true;
            foreach (DataRow dr in dt.Rows)
            {
                string sql = "Insert into SalarySheetItem(Id,SheetId,EmployeeId,BaseSalary,Bonus,Fine,Other)Values(@Id,@SheetId,@EmployeeId,@BaseSalary,@Bonus,@Fine,@Other)";
                result = result&&SqlHelper.ExecuteNonQuery(sql, new SqlParameter[] {
                    new SqlParameter("@Id",Guid.NewGuid()),
                    new SqlParameter("@SheetId",salarySheet.Id),
                    new SqlParameter("@EmployeeId",dr[0]),
                   new SqlParameter("@BaseSalary", SqlDbType.Decimal)
                   {
                       Value=0,
                   },
                    new SqlParameter("@Bonus", SqlDbType.Decimal){Value=0},
                    new SqlParameter("@Fine", SqlDbType.Decimal) { Value = 0 },
                    new SqlParameter("@Other", SqlDbType.Decimal) { Value = 0 }
                }) > 0;
            }
            return result;
        }
        public static DataTable GetSalarySheetItem(SalarySheet salarySheet)
        {
            string sql = "Select SalarySheetItem.Id as ID,EmployeeID as 员工ID,Employee.Name as 姓名,BaseSalary as 基础工资,Bonus as 奖金 ,Fine as Fine,Other as 其他 From SalarySheetItem,Employee Where SheetId=@SheetId and Employee.Id=EmployeeId";
            return SqlHelper.GetDataTable(sql,new SqlParameter("@SheetId",salarySheet.Id));
        }
        public static DataTable GetSalarySheetItems(SalarySheet salarySheet)
        {
            string sql = "select SalarySheetItem.Id as 编号, Employee.Name as 姓名, SalarySheetItem.BaseSalary as 基本工资, SalarySheetItem.Bonus as 奖金, SalarySheetItem.Fine as 罚款, SalarySheetItem.Other as 其他 from SalarySheetItem, Employee where SheetId = @sheetId and SalarySheetItem.EmployeeId = Employee.Id";
            SqlParameter para = new SqlParameter("@sheetId", salarySheet.Id);
            return SqlHelper.GetDataTable(sql, para);
        }
        public static void DeleteSalarySheetAndSheetItem(SalarySheet salarySheet)
        {
            string sqlDeleteItem = "Delete From SalarySheetItem Where SheetId=@SheetId";
            SqlHelper.ExecuteNonQuery(sqlDeleteItem,new SqlParameter("@SheetId",salarySheet.Id));
            string sqlDeleteSheet = "Delete From SalarySheet Where Id=@Id";
            SqlHelper.ExecuteNonQuery(sqlDeleteSheet, new SqlParameter("@Id", salarySheet.Id));
        }
        public static SalarySheet GetSalarySheet(int year, int month, Guid departmentId)
        {
            string sql = "Select Id from SalarySheet Where Year=@Year and Month=@Month and DepartmentId=@DepartmentId";
            Guid sheetId;
            if (SqlHelper.ExecuteScalar(sql, new SqlParameter[] {
                new SqlParameter("@Year",year),
                new SqlParameter("@Month",month),
                new SqlParameter("DepartmentId",departmentId) }) != null) 
            {
                sheetId = (Guid)SqlHelper.ExecuteScalar(sql, new SqlParameter[]{
                new SqlParameter("@Year",year),
                new SqlParameter("@Month",month),
                new SqlParameter("DepartmentId",departmentId) });
            }
            else
            {
                sheetId = Guid.Empty;
            }

            if (sheetId != Guid.Empty)
            {
                return new SalarySheet()
                {
                    Id = sheetId,
                    Year = year,
                    Month = month,
                    DepartmentId = departmentId
                };
            }
            else
            {
                return null!;
            }
        }
        public static DataTable GetSalarySheetTable()
        {
            string sql = "SELECT Year AS 年, Month AS 月, Name AS 部门 From SalarySheet,Department WHERE Department.Id=DepartmentId";
            return SqlHelper.GetDataTable(sql);
        }
        public static void SaveSheetItem(SalarySheetItem sheetitem)
        {
            string sql = "update SalarySheetItem set BaseSalary = @base, Bonus = @bonus, Fine = @fine, Other = @other where Id = @id";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@base", sheetitem.BaseSalary),
                new SqlParameter("@bonus", sheetitem.Bonus),
                new SqlParameter("@fine", sheetitem.Fine),
                new SqlParameter("@other", sheetitem.Other),
                new SqlParameter("@id", sheetitem.Id),
            };
            SqlHelper.ExecuteNonQuery(sql, para);
        }
    }
}
